{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 音乐网站用户流失预测 -- 数据合并\n",
    "\n",
    "### 数据集说明\n",
    "\n",
    "项目提供KKBOX用户——歌曲重复播放记录，以及用户和歌曲的元数据。训练数据由2017年2月服务到期的用户构成，target标签代表用户在2017年3月是否续订了业务。测试集中的数据由2017年3月内将到期的用户构成，需要预测用户是否在到期后的一个月内即2017年4月预定、流失的概率。\n",
    "\n",
    "以下是文件及字段说明：\n",
    "\n",
    "1. train.csv: 训练数据，共7,377,418条记录\n",
    "\n",
    "    msno: 用户id，加密String  \n",
    "\n",
    "    song_id: song id，歌曲id\n",
    "\n",
    "    source_system_tab: 触发事件的类型/tab，用于表示app的功能类型\n",
    "\n",
    "    source_screen_name: 用户看到的布局的名字（name of the layout）\n",
    "\n",
    "    source_type: 用户在app上播放音乐的入口的类型\n",
    "\n",
    "    target: 标签。1表示用户在第一次听音乐后会在一个月内继续订阅，0表示没有订阅。\n",
    "\n",
    "2. test.csv ：测试数据，共2,556,790条记录\n",
    "\n",
    "    id: id (用于结果提交)\n",
    "\n",
    "    msno: 用户id\n",
    "\n",
    "    song_id: 歌曲id\n",
    "\n",
    "    source_system_tab: 触发事件的类型/tab，用于表示app的功能类型\n",
    "\n",
    "    source_screen_name: 用户看到的布局的名字（name of the layout）\n",
    "\n",
    "    source_type: 用户在app上播放音乐的入口的类型\n",
    "\n",
    "3. sampleSubmission.csv：提交结果文件样例  \n",
    "\n",
    "    提交测试结果包含两个字段，分别为测试样本id及其标签为1的概率，格式如下：\n",
    "\n",
    "    id,target\n",
    "    \n",
    "    2,0.3\n",
    "    \n",
    "    5,0.1\n",
    "    \n",
    "    6,1\n",
    "    \n",
    "    etc.\n",
    "\n",
    "4. songs.csv：歌曲元数据信息，用unicode编码\n",
    "\n",
    "    song_id：歌曲id\n",
    "\n",
    "    song_length: 单位为ms\n",
    "\n",
    "    genre_ids: genre 类别. 可多选，用 “|“隔开\n",
    "\n",
    "    artist_name：歌手\n",
    "\n",
    "    composer：作曲\n",
    "\n",
    "    lyricist：作词\n",
    "\n",
    "    language：语言\n",
    "\n",
    "5. members.csv：用户元数据信息\n",
    "\n",
    "    msno：用户id\n",
    "\n",
    "    city：城市\n",
    "\n",
    "    bd: 年龄。注意：年龄数据有离群点\n",
    "\n",
    "    gender：性别\n",
    "\n",
    "    registered_via: 注册方式\n",
    "\n",
    "    registration_init_time: 注册时间，格式为%Y%m%d\n",
    "\n",
    "    expiration_date: 到期时间，格式为 %Y%m%d\n",
    "\n",
    "6. song_extra_infos.csv：歌曲额外的信息\n",
    "\n",
    "    song_id：歌曲id\n",
    "\n",
    "    song name ：歌曲名字\n",
    "\n",
    "    isrc – 国际标准音像制品编码(International Standard Recording Code )。理论上可用于歌曲id，但产生的ISR没有经过官方授权。因此ISRC中的信息，如国家代码和参考年份可能不正确。且多首歌曲可能共享共一个ISRC，因为一首歌曲的音像制可发行多次。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入工具包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 矩阵完整显示\n",
    "np.set_printoptions(threshold=np.inf)\n",
    "%matplotlib inline\n",
    "\n",
    "#load数据（用户和物品索引，以及倒排表）\n",
    "import pickle as cPickle\n",
    "#labelencoder\n",
    "from sklearn import preprocessing\n",
    "\n",
    "# 用labelencoder编码\n",
    "le = preprocessing.LabelEncoder()\n",
    "\n",
    "dpath = '../data/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Train数据合并（FE_Train.csv + FE_Members.csv+FE_Songs.csv）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "fe_train = pd.read_csv(dpath +'LR_data/FE_Train.csv')\n",
    "fe_member = pd.read_csv(dpath +'LR_data/FE_Members.csv')\n",
    "fe_song = pd.read_csv(dpath +'LR_data/FE_Songs.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7377418 entries, 0 to 7377417\n",
      "Data columns (total 6 columns):\n",
      "msno                          object\n",
      "song_id                       object\n",
      "source_system_tab_missing     int64\n",
      "source_screen_name_missing    int64\n",
      "source_type_missing           int64\n",
      "target                        int64\n",
      "dtypes: int64(4), object(2)\n",
      "memory usage: 337.7+ MB\n"
     ]
    }
   ],
   "source": [
    "fe_train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 34403 entries, 0 to 34402\n",
      "Data columns (total 42 columns):\n",
      "msno                   34403 non-null object\n",
      "city_1                 34403 non-null int64\n",
      "city_3                 34403 non-null int64\n",
      "city_4                 34403 non-null int64\n",
      "city_5                 34403 non-null int64\n",
      "city_6                 34403 non-null int64\n",
      "city_7                 34403 non-null int64\n",
      "city_8                 34403 non-null int64\n",
      "city_9                 34403 non-null int64\n",
      "city_10                34403 non-null int64\n",
      "city_11                34403 non-null int64\n",
      "city_12                34403 non-null int64\n",
      "city_13                34403 non-null int64\n",
      "city_14                34403 non-null int64\n",
      "city_15                34403 non-null int64\n",
      "city_16                34403 non-null int64\n",
      "city_17                34403 non-null int64\n",
      "city_18                34403 non-null int64\n",
      "city_19                34403 non-null int64\n",
      "city_20                34403 non-null int64\n",
      "city_21                34403 non-null int64\n",
      "city_22                34403 non-null int64\n",
      "bd_exception           34403 non-null int64\n",
      "gender                 34403 non-null int64\n",
      "registered_via_3       34403 non-null int64\n",
      "registered_via_4       34403 non-null int64\n",
      "registered_via_7       34403 non-null int64\n",
      "registered_via_9       34403 non-null int64\n",
      "registered_via_13      34403 non-null int64\n",
      "registered_via_16      34403 non-null int64\n",
      "date_diff_bin_0        34403 non-null int64\n",
      "date_diff_bin_5        34403 non-null int64\n",
      "date_diff_bin_10       34403 non-null int64\n",
      "date_diff_bin_30       34403 non-null int64\n",
      "date_diff_bin_183      34403 non-null int64\n",
      "date_diff_bin_365      34403 non-null int64\n",
      "date_diff_bin_730      34403 non-null int64\n",
      "date_diff_bin_1095     34403 non-null int64\n",
      "date_diff_bin_1825     34403 non-null int64\n",
      "date_diff_bin_2555     34403 non-null int64\n",
      "date_diff_bin_3650     34403 non-null int64\n",
      "date_diff_bin_99999    34403 non-null int64\n",
      "dtypes: int64(41), object(1)\n",
      "memory usage: 11.0+ MB\n"
     ]
    }
   ],
   "source": [
    "fe_member.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2296833 entries, 0 to 2296832\n",
      "Columns: 204 entries, song_id to language_59.0\n",
      "dtypes: int64(203), object(1)\n",
      "memory usage: 3.5+ GB\n"
     ]
    }
   ],
   "source": [
    "fe_song.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 左连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source_system_tab_missing</th>\n",
       "      <th>source_screen_name_missing</th>\n",
       "      <th>source_type_missing</th>\n",
       "      <th>target</th>\n",
       "      <th>city_1</th>\n",
       "      <th>city_3</th>\n",
       "      <th>city_4</th>\n",
       "      <th>city_5</th>\n",
       "      <th>...</th>\n",
       "      <th>date_diff_bin_10</th>\n",
       "      <th>date_diff_bin_30</th>\n",
       "      <th>date_diff_bin_183</th>\n",
       "      <th>date_diff_bin_365</th>\n",
       "      <th>date_diff_bin_730</th>\n",
       "      <th>date_diff_bin_1095</th>\n",
       "      <th>date_diff_bin_1825</th>\n",
       "      <th>date_diff_bin_2555</th>\n",
       "      <th>date_diff_bin_3650</th>\n",
       "      <th>date_diff_bin_99999</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3Hg5kugV1S0wzEVLAEfqjIV5UHzb7bCrdBRQlGygLvU=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>VkILU0H1h3NMmk9MQrXouNudGk5n8Ls5cqRRuBxeTh4=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>bPIvRTzfHxH5LgHrStll+tYwSQNVV8PySgA3M1PfTgc=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>uHqAtShXTRXju5GE8ri3ITsVFepPf8jUoCF7ffNOuqE=</td>\n",
       "      <td>/bU6IRSK+YNlNbaTkxo7bhsb2EDLPrnksdX3ggcZNhI=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>uHqAtShXTRXju5GE8ri3ITsVFepPf8jUoCF7ffNOuqE=</td>\n",
       "      <td>EbI7xoNxI+3QSsiHxL13zBdgHIJOwa3srHd7cDcnJ0g=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  \\\n",
       "0  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "5  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "6  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "7  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "8  uHqAtShXTRXju5GE8ri3ITsVFepPf8jUoCF7ffNOuqE=   \n",
       "9  uHqAtShXTRXju5GE8ri3ITsVFepPf8jUoCF7ffNOuqE=   \n",
       "\n",
       "                                        song_id  source_system_tab_missing  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=                          0   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=                          0   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=                          0   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=                          0   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=                          0   \n",
       "5  3Hg5kugV1S0wzEVLAEfqjIV5UHzb7bCrdBRQlGygLvU=                          0   \n",
       "6  VkILU0H1h3NMmk9MQrXouNudGk5n8Ls5cqRRuBxeTh4=                          0   \n",
       "7  bPIvRTzfHxH5LgHrStll+tYwSQNVV8PySgA3M1PfTgc=                          0   \n",
       "8  /bU6IRSK+YNlNbaTkxo7bhsb2EDLPrnksdX3ggcZNhI=                          0   \n",
       "9  EbI7xoNxI+3QSsiHxL13zBdgHIJOwa3srHd7cDcnJ0g=                          0   \n",
       "\n",
       "   source_screen_name_missing  source_type_missing  target  city_1  city_3  \\\n",
       "0                           0                    0       1       1       0   \n",
       "1                           0                    0       1       0       0   \n",
       "2                           0                    0       1       0       0   \n",
       "3                           0                    0       1       0       0   \n",
       "4                           0                    0       1       1       0   \n",
       "5                           0                    0       1       1       0   \n",
       "6                           0                    0       1       0       0   \n",
       "7                           0                    0       1       1       0   \n",
       "8                           0                    0       1       0       0   \n",
       "9                           0                    0       1       0       0   \n",
       "\n",
       "   city_4  city_5  ...  date_diff_bin_10  date_diff_bin_30  date_diff_bin_183  \\\n",
       "0       0       0  ...                 0                 0                  0   \n",
       "1       0       0  ...                 0                 0                  0   \n",
       "2       0       0  ...                 0                 0                  0   \n",
       "3       0       0  ...                 0                 0                  0   \n",
       "4       0       0  ...                 0                 0                  0   \n",
       "5       0       0  ...                 0                 0                  0   \n",
       "6       0       0  ...                 0                 0                  0   \n",
       "7       0       0  ...                 0                 0                  0   \n",
       "8       0       0  ...                 0                 0                  0   \n",
       "9       0       0  ...                 0                 0                  0   \n",
       "\n",
       "   date_diff_bin_365  date_diff_bin_730  date_diff_bin_1095  \\\n",
       "0                  0                  0                   0   \n",
       "1                  0                  0                   0   \n",
       "2                  0                  0                   0   \n",
       "3                  0                  0                   0   \n",
       "4                  0                  0                   0   \n",
       "5                  0                  0                   0   \n",
       "6                  0                  0                   0   \n",
       "7                  0                  0                   0   \n",
       "8                  0                  0                   0   \n",
       "9                  0                  0                   0   \n",
       "\n",
       "   date_diff_bin_1825  date_diff_bin_2555  date_diff_bin_3650  \\\n",
       "0                   0                   1                   0   \n",
       "1                   0                   1                   0   \n",
       "2                   0                   1                   0   \n",
       "3                   0                   1                   0   \n",
       "4                   0                   1                   0   \n",
       "5                   0                   1                   0   \n",
       "6                   0                   1                   0   \n",
       "7                   0                   1                   0   \n",
       "8                   0                   1                   0   \n",
       "9                   0                   1                   0   \n",
       "\n",
       "   date_diff_bin_99999  \n",
       "0                    0  \n",
       "1                    0  \n",
       "2                    0  \n",
       "3                    0  \n",
       "4                    0  \n",
       "5                    0  \n",
       "6                    0  \n",
       "7                    0  \n",
       "8                    0  \n",
       "9                    0  \n",
       "\n",
       "[10 rows x 47 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据合并--左链接\n",
    "df_train_member_left = pd.merge(fe_train, fe_member, how='left', on='msno', sort=False)\n",
    "df_train_member_left.head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7377418, 47)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看数据合并后的数据集大小\n",
    "df_train_member_left.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source_system_tab_missing</th>\n",
       "      <th>source_screen_name_missing</th>\n",
       "      <th>source_type_missing</th>\n",
       "      <th>target</th>\n",
       "      <th>city_1</th>\n",
       "      <th>city_3</th>\n",
       "      <th>city_4</th>\n",
       "      <th>city_5</th>\n",
       "      <th>...</th>\n",
       "      <th>language_-1.0</th>\n",
       "      <th>language_3.0</th>\n",
       "      <th>language_10.0</th>\n",
       "      <th>language_17.0</th>\n",
       "      <th>language_24.0</th>\n",
       "      <th>language_31.0</th>\n",
       "      <th>language_38.0</th>\n",
       "      <th>language_45.0</th>\n",
       "      <th>language_52.0</th>\n",
       "      <th>language_59.0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>3Hg5kugV1S0wzEVLAEfqjIV5UHzb7bCrdBRQlGygLvU=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=</td>\n",
       "      <td>VkILU0H1h3NMmk9MQrXouNudGk5n8Ls5cqRRuBxeTh4=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=</td>\n",
       "      <td>bPIvRTzfHxH5LgHrStll+tYwSQNVV8PySgA3M1PfTgc=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>uHqAtShXTRXju5GE8ri3ITsVFepPf8jUoCF7ffNOuqE=</td>\n",
       "      <td>/bU6IRSK+YNlNbaTkxo7bhsb2EDLPrnksdX3ggcZNhI=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>uHqAtShXTRXju5GE8ri3ITsVFepPf8jUoCF7ffNOuqE=</td>\n",
       "      <td>EbI7xoNxI+3QSsiHxL13zBdgHIJOwa3srHd7cDcnJ0g=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 250 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  \\\n",
       "0  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "1  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "2  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "3  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "4  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "5  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "6  Xumu+NIjS6QYVxDS4/t3SawvJ7viT9hPKXmf0RtLNx8=   \n",
       "7  FGtllVqz18RPiwJj/edr2gV78zirAiY/9SmYvia+kCg=   \n",
       "8  uHqAtShXTRXju5GE8ri3ITsVFepPf8jUoCF7ffNOuqE=   \n",
       "9  uHqAtShXTRXju5GE8ri3ITsVFepPf8jUoCF7ffNOuqE=   \n",
       "\n",
       "                                        song_id  source_system_tab_missing  \\\n",
       "0  BBzumQNXUHKdEBOB7mAJuzok+IJA1c2Ryg/yzTF6tik=                          0   \n",
       "1  bhp/MpSNoqoxOIB+/l8WPqu6jldth4DIpCm3ayXnJqM=                          0   \n",
       "2  JNWfrrC7zNN7BdMpsISKa4Mw+xVJYNnxXh3/Epw7QgY=                          0   \n",
       "3  2A87tzfnJTSWqD7gIZHisolhe4DMdzkbd6LzO1KHjNs=                          0   \n",
       "4  3qm6XTZ6MOCU11x8FIVbAGH5l5uMkT3/ZalWG1oo2Gc=                          0   \n",
       "5  3Hg5kugV1S0wzEVLAEfqjIV5UHzb7bCrdBRQlGygLvU=                          0   \n",
       "6  VkILU0H1h3NMmk9MQrXouNudGk5n8Ls5cqRRuBxeTh4=                          0   \n",
       "7  bPIvRTzfHxH5LgHrStll+tYwSQNVV8PySgA3M1PfTgc=                          0   \n",
       "8  /bU6IRSK+YNlNbaTkxo7bhsb2EDLPrnksdX3ggcZNhI=                          0   \n",
       "9  EbI7xoNxI+3QSsiHxL13zBdgHIJOwa3srHd7cDcnJ0g=                          0   \n",
       "\n",
       "   source_screen_name_missing  source_type_missing  target  city_1  city_3  \\\n",
       "0                           0                    0       1       1       0   \n",
       "1                           0                    0       1       0       0   \n",
       "2                           0                    0       1       0       0   \n",
       "3                           0                    0       1       0       0   \n",
       "4                           0                    0       1       1       0   \n",
       "5                           0                    0       1       1       0   \n",
       "6                           0                    0       1       0       0   \n",
       "7                           0                    0       1       1       0   \n",
       "8                           0                    0       1       0       0   \n",
       "9                           0                    0       1       0       0   \n",
       "\n",
       "   city_4  city_5  ...  language_-1.0  language_3.0  language_10.0  \\\n",
       "0       0       0  ...            0.0           0.0            0.0   \n",
       "1       0       0  ...            0.0           0.0            0.0   \n",
       "2       0       0  ...            0.0           0.0            0.0   \n",
       "3       0       0  ...            1.0           0.0            0.0   \n",
       "4       0       0  ...            0.0           0.0            0.0   \n",
       "5       0       0  ...            0.0           0.0            0.0   \n",
       "6       0       0  ...            0.0           0.0            0.0   \n",
       "7       0       0  ...            0.0           0.0            0.0   \n",
       "8       0       0  ...            0.0           0.0            0.0   \n",
       "9       0       0  ...            0.0           0.0            0.0   \n",
       "\n",
       "   language_17.0  language_24.0  language_31.0  language_38.0  language_45.0  \\\n",
       "0            0.0            0.0            0.0            0.0            0.0   \n",
       "1            0.0            0.0            0.0            0.0            0.0   \n",
       "2            0.0            0.0            0.0            0.0            0.0   \n",
       "3            0.0            0.0            0.0            0.0            0.0   \n",
       "4            0.0            0.0            0.0            0.0            0.0   \n",
       "5            0.0            0.0            0.0            0.0            0.0   \n",
       "6            0.0            0.0            1.0            0.0            0.0   \n",
       "7            0.0            0.0            0.0            0.0            0.0   \n",
       "8            0.0            0.0            0.0            0.0            0.0   \n",
       "9            0.0            0.0            0.0            0.0            0.0   \n",
       "\n",
       "   language_52.0  language_59.0  \n",
       "0            1.0            0.0  \n",
       "1            1.0            0.0  \n",
       "2            1.0            0.0  \n",
       "3            0.0            0.0  \n",
       "4            1.0            0.0  \n",
       "5            1.0            0.0  \n",
       "6            0.0            0.0  \n",
       "7            1.0            0.0  \n",
       "8            1.0            0.0  \n",
       "9            1.0            0.0  \n",
       "\n",
       "[10 rows x 250 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_member_song_left = pd.merge(df_train_member_left, fe_song, how='left', on='song_id', sort=False)\n",
    "df_train_member_song_left.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7377418, 250)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_member_song_left.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存合并后的结果文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存结果到文件\n",
    "df_train_member_song_left.to_csv(dpath + 'LR_data/Merge_Train.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Test数据合并（FE_Test.csv + FE_Members.csv+FE_Songs.csv）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "fe_test = pd.read_csv(dpath +'LR_data/FE_Test.csv')\n",
    "fe_member = pd.read_csv(dpath +'LR_data/FE_Members.csv')\n",
    "fe_song = pd.read_csv(dpath +'LR_data/FE_Songs.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 左连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source_system_tab_missing</th>\n",
       "      <th>source_screen_name_missing</th>\n",
       "      <th>source_type_missing</th>\n",
       "      <th>city_1</th>\n",
       "      <th>city_3</th>\n",
       "      <th>city_4</th>\n",
       "      <th>city_5</th>\n",
       "      <th>...</th>\n",
       "      <th>date_diff_bin_10</th>\n",
       "      <th>date_diff_bin_30</th>\n",
       "      <th>date_diff_bin_183</th>\n",
       "      <th>date_diff_bin_365</th>\n",
       "      <th>date_diff_bin_730</th>\n",
       "      <th>date_diff_bin_1095</th>\n",
       "      <th>date_diff_bin_1825</th>\n",
       "      <th>date_diff_bin_2555</th>\n",
       "      <th>date_diff_bin_3650</th>\n",
       "      <th>date_diff_bin_99999</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=</td>\n",
       "      <td>WmHKgKMlp1lQMecNdNvDMkvIycZYHnFwDT72I5sIssc=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=</td>\n",
       "      <td>y/rsZ9DC7FwK5F2PK2D5mj+aOBUJAjuu3dZ14NgE0vM=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>/uQAlrAkaczV+nWCd2sPF2ekvXPRipV7q0l+gbLuxjw=</td>\n",
       "      <td>8eZLFOdGVdXBSqoAv5nsLigeH2BvKXzTQYtUM53I0k4=</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>ztCf8thYsS4YN3GcIL/bvoxLm/T5mYBVKOO4C9NiVfQ=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>MKVMpslKcQhMaFEgcEQhEfi5+RZhMYlU3eRDpySrH8Y=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>NV3nhEcMqsawwvSNTUAt9IVAexHLOm0lDfrHyEfN5B0=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>DPM6G9RB5QO2dvAVPyc70gxHdvu872IjTUvV6LJT8ho=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>JGXB3PHc0CX0JskwwjmYP8i318BLo7DhChgEj6Yqjt8=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>507plIkmke1jh3wMrHqKore82pPFozADwydR8P0Gx2Q=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>3ZQ6oGfcSiUoCrtBPKGa8hHCiFh5jqtDqPVDUl/zrjU=</td>\n",
       "      <td>HsgJXv1C7iVZiP7CWwWTfsmMhia6Huc0MUccfj+D02o=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                          msno  \\\n",
       "0   0  V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=   \n",
       "1   1  V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=   \n",
       "2   2  /uQAlrAkaczV+nWCd2sPF2ekvXPRipV7q0l+gbLuxjw=   \n",
       "3   3  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "4   4  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "5   5  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "6   6  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "7   7  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "8   8  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "9   9  3ZQ6oGfcSiUoCrtBPKGa8hHCiFh5jqtDqPVDUl/zrjU=   \n",
       "\n",
       "                                        song_id  source_system_tab_missing  \\\n",
       "0  WmHKgKMlp1lQMecNdNvDMkvIycZYHnFwDT72I5sIssc=                          0   \n",
       "1  y/rsZ9DC7FwK5F2PK2D5mj+aOBUJAjuu3dZ14NgE0vM=                          0   \n",
       "2  8eZLFOdGVdXBSqoAv5nsLigeH2BvKXzTQYtUM53I0k4=                          0   \n",
       "3  ztCf8thYsS4YN3GcIL/bvoxLm/T5mYBVKOO4C9NiVfQ=                          0   \n",
       "4  MKVMpslKcQhMaFEgcEQhEfi5+RZhMYlU3eRDpySrH8Y=                          0   \n",
       "5  NV3nhEcMqsawwvSNTUAt9IVAexHLOm0lDfrHyEfN5B0=                          0   \n",
       "6  DPM6G9RB5QO2dvAVPyc70gxHdvu872IjTUvV6LJT8ho=                          0   \n",
       "7  JGXB3PHc0CX0JskwwjmYP8i318BLo7DhChgEj6Yqjt8=                          0   \n",
       "8  507plIkmke1jh3wMrHqKore82pPFozADwydR8P0Gx2Q=                          0   \n",
       "9  HsgJXv1C7iVZiP7CWwWTfsmMhia6Huc0MUccfj+D02o=                          0   \n",
       "\n",
       "   source_screen_name_missing  source_type_missing  city_1  city_3  city_4  \\\n",
       "0                           0                    0       1       0       0   \n",
       "1                           0                    0       1       0       0   \n",
       "2                           1                    0       1       0       0   \n",
       "3                           0                    0       0       1       0   \n",
       "4                           0                    0       0       1       0   \n",
       "5                           0                    0       0       1       0   \n",
       "6                           0                    0       0       1       0   \n",
       "7                           0                    0       0       1       0   \n",
       "8                           0                    0       0       1       0   \n",
       "9                           0                    0       1       0       0   \n",
       "\n",
       "   city_5  ...  date_diff_bin_10  date_diff_bin_30  date_diff_bin_183  \\\n",
       "0       0  ...                 0                 0                  0   \n",
       "1       0  ...                 0                 0                  0   \n",
       "2       0  ...                 1                 0                  0   \n",
       "3       0  ...                 0                 0                  0   \n",
       "4       0  ...                 0                 0                  0   \n",
       "5       0  ...                 0                 0                  0   \n",
       "6       0  ...                 0                 0                  0   \n",
       "7       0  ...                 0                 0                  0   \n",
       "8       0  ...                 0                 0                  0   \n",
       "9       0  ...                 0                 0                  0   \n",
       "\n",
       "   date_diff_bin_365  date_diff_bin_730  date_diff_bin_1095  \\\n",
       "0                  0                  1                   0   \n",
       "1                  0                  1                   0   \n",
       "2                  0                  0                   0   \n",
       "3                  0                  0                   0   \n",
       "4                  0                  0                   0   \n",
       "5                  0                  0                   0   \n",
       "6                  0                  0                   0   \n",
       "7                  0                  0                   0   \n",
       "8                  0                  0                   0   \n",
       "9                  0                  1                   0   \n",
       "\n",
       "   date_diff_bin_1825  date_diff_bin_2555  date_diff_bin_3650  \\\n",
       "0                   0                   0                   0   \n",
       "1                   0                   0                   0   \n",
       "2                   0                   0                   0   \n",
       "3                   0                   0                   1   \n",
       "4                   0                   0                   1   \n",
       "5                   0                   0                   1   \n",
       "6                   0                   0                   1   \n",
       "7                   0                   0                   1   \n",
       "8                   0                   0                   1   \n",
       "9                   0                   0                   0   \n",
       "\n",
       "   date_diff_bin_99999  \n",
       "0                    0  \n",
       "1                    0  \n",
       "2                    0  \n",
       "3                    0  \n",
       "4                    0  \n",
       "5                    0  \n",
       "6                    0  \n",
       "7                    0  \n",
       "8                    0  \n",
       "9                    0  \n",
       "\n",
       "[10 rows x 47 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据合并--左链接\n",
    "df_test_member_left = pd.merge(fe_test, fe_member, how='left', on='msno', sort=False)\n",
    "df_test_member_left.head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>msno</th>\n",
       "      <th>song_id</th>\n",
       "      <th>source_system_tab_missing</th>\n",
       "      <th>source_screen_name_missing</th>\n",
       "      <th>source_type_missing</th>\n",
       "      <th>city_1</th>\n",
       "      <th>city_3</th>\n",
       "      <th>city_4</th>\n",
       "      <th>city_5</th>\n",
       "      <th>...</th>\n",
       "      <th>language_-1.0</th>\n",
       "      <th>language_3.0</th>\n",
       "      <th>language_10.0</th>\n",
       "      <th>language_17.0</th>\n",
       "      <th>language_24.0</th>\n",
       "      <th>language_31.0</th>\n",
       "      <th>language_38.0</th>\n",
       "      <th>language_45.0</th>\n",
       "      <th>language_52.0</th>\n",
       "      <th>language_59.0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=</td>\n",
       "      <td>WmHKgKMlp1lQMecNdNvDMkvIycZYHnFwDT72I5sIssc=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=</td>\n",
       "      <td>y/rsZ9DC7FwK5F2PK2D5mj+aOBUJAjuu3dZ14NgE0vM=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>/uQAlrAkaczV+nWCd2sPF2ekvXPRipV7q0l+gbLuxjw=</td>\n",
       "      <td>8eZLFOdGVdXBSqoAv5nsLigeH2BvKXzTQYtUM53I0k4=</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>ztCf8thYsS4YN3GcIL/bvoxLm/T5mYBVKOO4C9NiVfQ=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>MKVMpslKcQhMaFEgcEQhEfi5+RZhMYlU3eRDpySrH8Y=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>NV3nhEcMqsawwvSNTUAt9IVAexHLOm0lDfrHyEfN5B0=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>DPM6G9RB5QO2dvAVPyc70gxHdvu872IjTUvV6LJT8ho=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>JGXB3PHc0CX0JskwwjmYP8i318BLo7DhChgEj6Yqjt8=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=</td>\n",
       "      <td>507plIkmke1jh3wMrHqKore82pPFozADwydR8P0Gx2Q=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>3ZQ6oGfcSiUoCrtBPKGa8hHCiFh5jqtDqPVDUl/zrjU=</td>\n",
       "      <td>HsgJXv1C7iVZiP7CWwWTfsmMhia6Huc0MUccfj+D02o=</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 250 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                          msno  \\\n",
       "0   0  V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=   \n",
       "1   1  V8ruy7SGk7tDm3zA51DPpn6qutt+vmKMBKa21dp54uM=   \n",
       "2   2  /uQAlrAkaczV+nWCd2sPF2ekvXPRipV7q0l+gbLuxjw=   \n",
       "3   3  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "4   4  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "5   5  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "6   6  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "7   7  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "8   8  1a6oo/iXKatxQx4eS9zTVD+KlSVaAFbTIqVvwLC1Y0k=   \n",
       "9   9  3ZQ6oGfcSiUoCrtBPKGa8hHCiFh5jqtDqPVDUl/zrjU=   \n",
       "\n",
       "                                        song_id  source_system_tab_missing  \\\n",
       "0  WmHKgKMlp1lQMecNdNvDMkvIycZYHnFwDT72I5sIssc=                          0   \n",
       "1  y/rsZ9DC7FwK5F2PK2D5mj+aOBUJAjuu3dZ14NgE0vM=                          0   \n",
       "2  8eZLFOdGVdXBSqoAv5nsLigeH2BvKXzTQYtUM53I0k4=                          0   \n",
       "3  ztCf8thYsS4YN3GcIL/bvoxLm/T5mYBVKOO4C9NiVfQ=                          0   \n",
       "4  MKVMpslKcQhMaFEgcEQhEfi5+RZhMYlU3eRDpySrH8Y=                          0   \n",
       "5  NV3nhEcMqsawwvSNTUAt9IVAexHLOm0lDfrHyEfN5B0=                          0   \n",
       "6  DPM6G9RB5QO2dvAVPyc70gxHdvu872IjTUvV6LJT8ho=                          0   \n",
       "7  JGXB3PHc0CX0JskwwjmYP8i318BLo7DhChgEj6Yqjt8=                          0   \n",
       "8  507plIkmke1jh3wMrHqKore82pPFozADwydR8P0Gx2Q=                          0   \n",
       "9  HsgJXv1C7iVZiP7CWwWTfsmMhia6Huc0MUccfj+D02o=                          0   \n",
       "\n",
       "   source_screen_name_missing  source_type_missing  city_1  city_3  city_4  \\\n",
       "0                           0                    0       1       0       0   \n",
       "1                           0                    0       1       0       0   \n",
       "2                           1                    0       1       0       0   \n",
       "3                           0                    0       0       1       0   \n",
       "4                           0                    0       0       1       0   \n",
       "5                           0                    0       0       1       0   \n",
       "6                           0                    0       0       1       0   \n",
       "7                           0                    0       0       1       0   \n",
       "8                           0                    0       0       1       0   \n",
       "9                           0                    0       1       0       0   \n",
       "\n",
       "   city_5  ...  language_-1.0  language_3.0  language_10.0  language_17.0  \\\n",
       "0       0  ...            0.0           1.0            0.0            0.0   \n",
       "1       0  ...            0.0           1.0            0.0            0.0   \n",
       "2       0  ...            0.0           0.0            0.0            1.0   \n",
       "3       0  ...            0.0           0.0            0.0            0.0   \n",
       "4       0  ...            1.0           0.0            0.0            0.0   \n",
       "5       0  ...            0.0           0.0            0.0            0.0   \n",
       "6       0  ...            0.0           0.0            0.0            0.0   \n",
       "7       0  ...            0.0           1.0            0.0            0.0   \n",
       "8       0  ...            0.0           1.0            0.0            0.0   \n",
       "9       0  ...            0.0           0.0            0.0            0.0   \n",
       "\n",
       "   language_24.0  language_31.0  language_38.0  language_45.0  language_52.0  \\\n",
       "0            0.0            0.0            0.0            0.0            0.0   \n",
       "1            0.0            0.0            0.0            0.0            0.0   \n",
       "2            0.0            0.0            0.0            0.0            0.0   \n",
       "3            0.0            0.0            0.0            0.0            1.0   \n",
       "4            0.0            0.0            0.0            0.0            0.0   \n",
       "5            0.0            1.0            0.0            0.0            0.0   \n",
       "6            0.0            1.0            0.0            0.0            0.0   \n",
       "7            0.0            0.0            0.0            0.0            0.0   \n",
       "8            0.0            0.0            0.0            0.0            0.0   \n",
       "9            0.0            1.0            0.0            0.0            0.0   \n",
       "\n",
       "   language_59.0  \n",
       "0            0.0  \n",
       "1            0.0  \n",
       "2            0.0  \n",
       "3            0.0  \n",
       "4            0.0  \n",
       "5            0.0  \n",
       "6            0.0  \n",
       "7            0.0  \n",
       "8            0.0  \n",
       "9            0.0  \n",
       "\n",
       "[10 rows x 250 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test_member_song_left = pd.merge(df_test_member_left, fe_song, how='left', on='song_id', sort=False)\n",
    "df_test_member_song_left.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存合并后的结果文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存结果到文件\n",
    "df_test_member_song_left.to_csv(dpath + 'LR_data/Merge_Test.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 最后将train和test数据集的msno和song_id转化成数值类型，并且标准化"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Train数据集的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_train = pd.read_csv(dpath +'LR_data/Merge_Train.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_msno_le = le.fit_transform(merge_train['msno'])\n",
    "train_song_id_le = le.fit_transform(merge_train['song_id'])\n",
    "\n",
    "df_train_msno_label = pd.DataFrame(data=train_msno_le, columns=['msno_label'])\n",
    "df_train_song_id_label = pd.DataFrame(data=train_song_id_le, columns=['song_id_label'])\n",
    "\n",
    "merge_train_tmp = merge_train.drop(['msno','song_id'], axis=1)\n",
    "# 合并在一起\n",
    "merge_train_data = pd.concat([df_train_msno_label, df_train_song_id_label, merge_train_tmp], axis = 1, ignore_index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "msno_label                    0\n",
       "song_id_label                 0\n",
       "source_system_tab_missing     0\n",
       "source_screen_name_missing    0\n",
       "source_type_missing           0\n",
       "target                        0\n",
       "city_1                        0\n",
       "city_3                        0\n",
       "city_4                        0\n",
       "city_5                        0\n",
       "city_6                        0\n",
       "city_7                        0\n",
       "city_8                        0\n",
       "city_9                        0\n",
       "city_10                       0\n",
       "city_11                       0\n",
       "city_12                       0\n",
       "city_13                       0\n",
       "city_14                       0\n",
       "city_15                       0\n",
       "city_16                       0\n",
       "city_17                       0\n",
       "city_18                       0\n",
       "city_19                       0\n",
       "city_20                       0\n",
       "city_21                       0\n",
       "city_22                       0\n",
       "bd_exception                  0\n",
       "gender                        0\n",
       "registered_via_3              0\n",
       "                             ..\n",
       "genre_ids_matrix_173          2\n",
       "genre_ids_matrix_174          2\n",
       "genre_ids_matrix_175          2\n",
       "genre_ids_matrix_176          2\n",
       "genre_ids_matrix_177          2\n",
       "genre_ids_matrix_178          2\n",
       "genre_ids_matrix_179          2\n",
       "genre_ids_matrix_180          2\n",
       "genre_ids_matrix_181          2\n",
       "genre_ids_matrix_182          2\n",
       "genre_ids_matrix_183          2\n",
       "genre_ids_matrix_184          2\n",
       "genre_ids_matrix_185          2\n",
       "genre_ids_matrix_186          2\n",
       "genre_ids_matrix_187          2\n",
       "genre_ids_matrix_188          2\n",
       "genre_ids_matrix_189          2\n",
       "genre_ids_matrix_190          2\n",
       "genre_ids_matrix_191          2\n",
       "artist_name_label             2\n",
       "language_-1.0                 2\n",
       "language_3.0                  2\n",
       "language_10.0                 2\n",
       "language_17.0                 2\n",
       "language_24.0                 2\n",
       "language_31.0                 2\n",
       "language_38.0                 2\n",
       "language_45.0                 2\n",
       "language_52.0                 2\n",
       "language_59.0                 2\n",
       "Length: 250, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 检查数据是否有nan值\n",
    "merge_train_data.apply(lambda x: sum(x.isnull()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Int64Index([1487159, 1942576], dtype='int64')\n"
     ]
    }
   ],
   "source": [
    "# 找出有空值的行\n",
    "index = merge_train_data['language_3.0'].index[merge_train_data['language_3.0'].apply(np.isnan)]\n",
    "print(index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 删除有空值的行\n",
    "merge_train_data = merge_train_data.drop(index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存Train结果到文件\n",
    "merge_train_data.to_csv(dpath + 'LR_data/Merge_Train_Org.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 数据缩放：由于数据极度稀疏，数据缩放应采用MinMaxScaler，使得变换后的数据继续保持稀疏。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_org = pd.read_csv(dpath +'LR_data/Merge_Train_Org.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分隔标签和x\n",
    "y_train = train_org['target']\n",
    "X_train = train_org.drop([\"target\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/sklearn/preprocessing/data.py:334: DataConversionWarning: Data with input dtype int64, float64 were all converted to float64 by MinMaxScaler.\n",
      "  return self.partial_fit(X, y)\n"
     ]
    }
   ],
   "source": [
    "# 对原始数据缩放\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "# 构造输入特征的标准化器\n",
    "ms_org = MinMaxScaler()\n",
    "\n",
    "#保存特征名字，用于结果保存为csv\n",
    "feat_names_org = X_train.columns\n",
    "\n",
    "# 用训练训练模型（得到均值和标准差）：fit\n",
    "# 并对训练数据进行特征缩放：transform\n",
    "X_train = ms_org.fit_transform(X_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#保存原始特征\n",
    "y = pd.Series(data = y_train, name = 'target')\n",
    "train_org_final = pd.concat([pd.DataFrame(columns = feat_names_org, data = X_train), y], axis = 1)\n",
    "train_org_final.to_csv(dpath +'LR_data/Merge_Train_Scaler.csv',index=False,header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 因为数据量太大，内存有限，每处理一个csv要重启一次释放内存。所以先保存特征编码过程中用到的模型，用于下面对测试数据的特征编码\n",
    "cPickle.dump(ms_org, open(dpath + \"LR_data/MinMaxSclaer_org.pkl\", 'wb'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test数据集的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_test = pd.read_csv(dpath +'LR_data/Merge_Test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_msno_le = le.fit_transform(merge_test['msno'])\n",
    "test_song_id_le = le.fit_transform(merge_test['song_id'])\n",
    "\n",
    "df_test_msno_label = pd.DataFrame(data=test_msno_le, columns=['msno_label'])\n",
    "df_test_song_id_label = pd.DataFrame(data=test_song_id_le, columns=['song_id_label'])\n",
    "\n",
    "merge_test_tmp = merge_test.drop(['msno','song_id'], axis=1)\n",
    "# 合并在一起\n",
    "merge_test_data = pd.concat([df_test_msno_label, df_test_song_id_label, merge_test_tmp], axis = 1, ignore_index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "msno_label                    0\n",
       "song_id_label                 0\n",
       "id                            0\n",
       "source_system_tab_missing     0\n",
       "source_screen_name_missing    0\n",
       "source_type_missing           0\n",
       "city_1                        0\n",
       "city_3                        0\n",
       "city_4                        0\n",
       "city_5                        0\n",
       "city_6                        0\n",
       "city_7                        0\n",
       "city_8                        0\n",
       "city_9                        0\n",
       "city_10                       0\n",
       "city_11                       0\n",
       "city_12                       0\n",
       "city_13                       0\n",
       "city_14                       0\n",
       "city_15                       0\n",
       "city_16                       0\n",
       "city_17                       0\n",
       "city_18                       0\n",
       "city_19                       0\n",
       "city_20                       0\n",
       "city_21                       0\n",
       "city_22                       0\n",
       "bd_exception                  0\n",
       "gender                        0\n",
       "registered_via_3              0\n",
       "                             ..\n",
       "genre_ids_matrix_173          2\n",
       "genre_ids_matrix_174          2\n",
       "genre_ids_matrix_175          2\n",
       "genre_ids_matrix_176          2\n",
       "genre_ids_matrix_177          2\n",
       "genre_ids_matrix_178          2\n",
       "genre_ids_matrix_179          2\n",
       "genre_ids_matrix_180          2\n",
       "genre_ids_matrix_181          2\n",
       "genre_ids_matrix_182          2\n",
       "genre_ids_matrix_183          2\n",
       "genre_ids_matrix_184          2\n",
       "genre_ids_matrix_185          2\n",
       "genre_ids_matrix_186          2\n",
       "genre_ids_matrix_187          2\n",
       "genre_ids_matrix_188          2\n",
       "genre_ids_matrix_189          2\n",
       "genre_ids_matrix_190          2\n",
       "genre_ids_matrix_191          2\n",
       "artist_name_label             2\n",
       "language_-1.0                 2\n",
       "language_3.0                  2\n",
       "language_10.0                 2\n",
       "language_17.0                 2\n",
       "language_24.0                 2\n",
       "language_31.0                 2\n",
       "language_38.0                 2\n",
       "language_45.0                 2\n",
       "language_52.0                 2\n",
       "language_59.0                 2\n",
       "Length: 250, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 检查数据是否有nan值\n",
    "merge_test_data.apply(lambda x: sum(x.isnull()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Int64Index([11519, 310005], dtype='int64')\n"
     ]
    }
   ],
   "source": [
    "# 找出有空值的行\n",
    "index = merge_test_data['language_3.0'].index[merge_test_data['language_3.0'].apply(np.isnan)]\n",
    "print(index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 删除有空值的行\n",
    "merge_test_data = merge_test_data.drop(index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存Test结果到文件\n",
    "merge_test_data.to_csv(dpath + 'LR_data/Merge_Test_Org.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 数据缩放：由于数据极度稀疏，数据缩放应采用MinMaxScaler，使得变换后的数据继续保持稀疏。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "X_test = pd.read_csv(dpath + 'LR_data/Merge_Test_Org.csv')\n",
    "ms_org = cPickle.load(open(dpath + \"LR_data/MinMaxSclaer_org.pkl\", 'rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_id = X_test['id']\n",
    "X_test = X_test.drop([\"id\"], axis=1)\n",
    "# 保存特征名字，用于结果保存为csv\n",
    "feat_names_org = X_test.columns\n",
    "X_test = ms_org.transform(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存原始特征\n",
    "test_org = pd.concat([test_id, pd.DataFrame(columns = feat_names_org, data = X_test)], axis = 1)\n",
    "test_org.to_csv(dpath +'LR_data/Merge_Test_Scaler.csv', index=False, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
